Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration.ConfigurationManager
Public Class SqlString
    Dim cmd As New SqlCommand
    Dim adp As New SqlDataAdapter
    'Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\shop.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    'Dim cn As New SqlConnection("Data Source=Krupa-PC;Initial Catalog=j1;uid=sa;password=sa;")
    'Dim cn As New SqlConnection("Data Source=SAI-D2190A07F04\SQLEXPRESS;Initial Catalog=j1;Integrated Security=True")'
    Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("ShoppingCartConnectionString").ConnectionString)
    Public Sub SqlString()
        Try
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
        Catch ex As Exception

        End Try

    End Sub
    '#####################################
    'Scalar Query
    '####################################
    Public Function executeCount(ByVal query As String) As Integer
        Dim cnt As Integer
        Try
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd = New SqlCommand(query, cn)
            cnt = cmd.ExecuteScalar()
            'ds.Tables(0).Columns
        Catch ex As Exception
            MsgBox(ex.Message)
            ' Exit Function
        End Try
        Return cnt
    End Function
    '##############################################################################
    '    INSERT QUERY
    '##############################################################################
    Public Function executeinsertquery(ByVal query As String) As Boolean
        If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        cmd = New SqlCommand(query, cn)
        cmd.ExecuteNonQuery()
        Return True
    End Function

    '##############################################################################
    '    SELECT QUERY
    '##############################################################################
    Public Function executeselectquery(ByVal query As String) As DataTable
        Dim dt As New DataTable
        Dim ds1 As New DataSet
        Try
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            adp = New SqlDataAdapter(query, cn)
            ds1.Clear()
            adp.Fill(ds1)
            dt = ds1.Tables(0)
            'cmd.Connection = cn
            'cmd.CommandText = query
            'cmd.ExecuteNonQuery()
            'adp.SelectCommand = cmd
            'adp.Fill(ds)
            'dt = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return dt
    End Function
    '##############################################################################
    '    DELETE QUERY
    '##############################################################################
    Public Function executeDeleteQuery(ByVal _query As String) As Boolean
        Try
            If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd.Connection = cn
            cmd.CommandText = _query
            adp.DeleteCommand = cmd
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return True
    End Function

End Class
